﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace OpenXmlSdkSample
{
    public class ExcelExport
    {
        #region Public Methods
        public void ExportDataTable(DataTable table, string exportFile)
        {
            ExcelDocument excelDocument = new ExcelDocument();
            excelDocument.CreatePackage(exportFile);

            using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(exportFile, true))
            {
                WorkbookPart workbookPart = spreadsheet.WorkbookPart;
                WorksheetPart worksheetPart = workbookPart.WorksheetParts.Last();
                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                //表头
                Row header = new Row();
                header.RowIndex = (UInt32)1;
                foreach (DataColumn column in table.Columns)
                {
                    Cell headerCell = createTextCell(table.Columns.IndexOf(column) + 1, 1, column.ColumnName);
                    headerCell.StyleIndex = (UInt32Value)1U;
                    header.AppendChild(headerCell);
                }
                sheetData.AppendChild(header);

                //数据行
                DataRow dataRow;
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    dataRow = table.Rows[i];
                    sheetData.AppendChild(createContentRow(dataRow, i + 2));
                }

                //合并单元格
                List<string> mergeCellsList = this.GetMergeRow(table, 0, table.Rows.Count - 1, 0, 2);
                if (mergeCellsList.Count > 0)
                {
                    MergeCells mergeCells;
                    if (worksheetPart.Worksheet.Elements<MergeCells>().Any())
                    {
                        mergeCells = worksheetPart.Worksheet.Elements<MergeCells>().First();
                    }
                    else
                    {
                        mergeCells = new MergeCells();

                        // Insert a MergeCells object into the specified position.
                        if (worksheetPart.Worksheet.Elements<CustomSheetView>().Any())
                        {
                            worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<CustomSheetView>().First());
                        }
                        else if (worksheetPart.Worksheet.Elements<DataConsolidate>().Any())
                        {
                            worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<DataConsolidate>().First());
                        }
                        else if (worksheetPart.Worksheet.Elements<SortState>().Any())
                        {
                            worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<SortState>().First());
                        }
                        else if (worksheetPart.Worksheet.Elements<AutoFilter>().Any())
                        {
                            worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<AutoFilter>().First());
                        }
                        else if (worksheetPart.Worksheet.Elements<Scenarios>().Any())
                        {
                            worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<Scenarios>().First());
                        }
                        else if (worksheetPart.Worksheet.Elements<ProtectedRanges>().Any())
                        {
                            worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<ProtectedRanges>().First());
                        }
                        else if (worksheetPart.Worksheet.Elements<SheetProtection>().Any())
                        {
                            worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<SheetProtection>().First());
                        }
                        else if (worksheetPart.Worksheet.Elements<SheetCalculationProperties>().Any())
                        {
                            worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<SheetCalculationProperties>().First());
                        }
                        else
                        {
                            worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<SheetData>().First());
                        }
                    }

                    foreach (var reference in mergeCellsList)
                    {
                        MergeCell cell = new MergeCell() { Reference = reference };
                        mergeCells.Append(cell);

                    }
                }


            }
        }

        #endregion

        #region WorkBook Methods

        /// <summary>
        /// Gets the Excel column name based on a supplied index number.
        /// </summary>
        /// <returns>1 = A, 2 = B... 27 = AA, etc.</returns>
        private string getColumnName(int columnIndex)
        {
            int dividend = columnIndex;
            string columnName = String.Empty;
            int modifier;

            while (dividend > 0)
            {
                modifier = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modifier).ToString() + columnName;
                dividend = (int)((dividend - modifier) / 26);
            }

            return columnName;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="columnIndex">从1开始</param>
        /// <param name="rowIndex">从1开始</param>
        /// <param name="cellValue">单元格内容</param>
        /// <returns></returns>
        private Cell createTextCell(int columnIndex, int rowIndex, object cellValue)
        {
            Cell cell = new Cell();

            cell.DataType = CellValues.InlineString;
            cell.CellReference = getColumnName(columnIndex) + rowIndex;

            InlineString inlineString = new InlineString();
            Text t = new Text();

            t.Text = cellValue.ToString();
            inlineString.AppendChild(t);
            cell.AppendChild(inlineString);

            return cell;
        }

        private Row createContentRow(DataRow dataRow, int rowIndex)
        {
            Row row = new Row
            {
                RowIndex = (UInt32)rowIndex
            };

            for (int i = 0; i < dataRow.Table.Columns.Count; i++)
            {
                Cell dataCell = createTextCell(i + 1, rowIndex, dataRow[i]);
                dataCell.StyleIndex = (UInt32Value)2U;
                row.AppendChild(dataCell);
            }
            return row;
        }

        #endregion

        #region 单元格合并方法
        /// <summary>
        /// 合并行
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="startRow">从0开始</param>
        /// <param name="endRow">小于dt.Rows.Count</param>
        /// <param name="startCol">从0开始</param>
        /// <param name="endCol">小于dt.Columns.Count</param>
        /// <param name="headers">表头级数,默认为1</param>
        /// <returns></returns>
        public List<string> GetMergeRow(DataTable dt, int startRow, int endRow, int startCol, int endCol, int headers = 1)
        {
            List<string> mergeCellList = new List<string>();
            if (dt != null && dt.Rows.Count > 0 && startRow < endRow && startCol <= endCol)
            {
                int index = startRow;
                string prevValue = string.Empty;
                for (int i = startRow; i <= endRow; i++)
                {
                    string currentValue = dt.Rows[i][startCol].ToString();
                    if ((prevValue != currentValue && i - index > 1))
                    {
                        //合并行
                        //MergeCell mergeCell1 = new MergeCell(){ Reference = "A2:A5" };
                        string merge = string.Format("{0}:{1}", ExcelHelper.GetCellReference(index + headers, startCol),
                            ExcelHelper.GetCellReference(i + headers - 1, startCol));
                        mergeCellList.Add(merge);
                        if (i - index > 1)
                        {
                            List<string> temp = GetMergeRow(dt, index, i - 1, startCol + 1, endCol, headers);
                            mergeCellList.AddRange(temp);
                        }

                        prevValue = currentValue;
                        index = i;
                    }
                    else if (prevValue == currentValue && i == endRow && i - index > 0)
                    {
                        //合并行
                        string merge = string.Format("{0}:{1}", ExcelHelper.GetCellReference(index + headers, startCol),
                            ExcelHelper.GetCellReference(i + headers, startCol));
                        mergeCellList.Add(merge);
                        if (i - index > 0)
                        {
                            List<string> temp = GetMergeRow(dt, index, i, startCol + 1, endCol, headers);
                            mergeCellList.AddRange(temp);
                        }

                        prevValue = currentValue;
                        index = i;
                    }
                    else if (prevValue != currentValue && i == index)
                    {
                        prevValue = currentValue;
                    }
                    else if (prevValue == currentValue)
                    {
                        //
                    }
                    else
                    {
                        prevValue = currentValue;
                        index++;
                    }
                }
            }

            return mergeCellList;
        }

        #endregion

    }
}

